﻿<%@ WebHandler Language="C#" Class="ContainerShipDashBoardSCB" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;
using System.Collections;

/**
 * Description: 转工看板（生产作业会）
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2022.8.24
 */

public class ContainerShipDashBoardSCB : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getJiJiaDetail":
                        r = getJiJiaDetail(jObject);//机加明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getRbDetail":
                        r = getRbDetail(jObject);//热表明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportJijiaDetail":
                        exportJijiaDetail(jObject);//导出机加明细
                        break;
                    case "exportRbDetail":
                        exportRbDetail(jObject);//导出热表明细
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //统计机加分厂数据
    string getJiJiaSql(JObject jObject)
    {
        string sql = @"
SELECT NVL(f.factoryname,t.factoryname) factoryname,t.fromworkcentername,t.productname,t.productdescription,t.containername,
       t.workflowstepname,t.receiver,t.recivedate,
       CASE WHEN t.denydate IS NOT NULL THEN '转工拒收'
            WHEN t.denydate IS NULL AND t.senddate IS NULL THEN '未转出'
            WHEN t.denydate IS NULL AND t.senddate IS NOT NULL AND t.recivedate IS NULL THEN '待接收'  
            WHEN t.denydate IS NULL AND t.recivedate IS NOT NULL AND t.completedate IS NULL THEN '待完成'  
            WHEN t.denydate IS NULL AND t.completedate IS NOT NULL AND t.shipindate IS NULL THEN '待转入'
            WHEN t.denydate IS NULL AND t.shipindate IS NOT NULL THEN '已转入' 
        END status,
        t.stepplancreatedate,t.senddate,t.stepplanshipdate,t.jjplaner,'已排产' planstatus,
        t.rbplaner,t.plannedstartdate,t.needcompletedate,t.estimatedcompletedate,t.completedate,
        t.shipindate,t.denydate,t.denyer,t.denyreason,
        CASE WHEN t.denydate IS NOT NULL THEN 0
             WHEN t.denydate IS NULL AND t.needcompletedate IS NULL THEN 0
             WHEN t.denydate IS NULL AND t.needcompletedate IS NOT NULL AND t.completedate IS NULL THEN TRUNC(SYSDATE) - TRUNC(t.needcompletedate)
             ELSE TRUNC(t.completedate) - TRUNC(t.needcompletedate)
        END delay,
        CASE WHEN t.recivedate IS NOT NULL THEN TRUNC(t.recivedate) - TRUNC(t.plannedstartdate)
            ELSE TRUNC(SYSDATE) - TRUNC(t.plannedstartdate)
        END senddelaydays,
        t.presend,
        t.plannedfinishdate,nt.notes
FROM r_shipboard_jijia t
LEFT JOIN mes_factory_mapping@insitedb74 f ON f.mes_factoryname = t.factoryname
LEFT JOIN mes_factory_mapping@insitedb74 f2 ON f2.mes_factoryname = t.tofactoryname
LEFT JOIN containerchecksuspending@insitedb74 nt ON nt.containername = t.containername AND nt.workflowstepname = t.workflowstepname AND nt.isuse = 0
WHERE TRUNC(t.plannedstartdate) >= TO_DATE('{0}','yyyy-mm-dd') AND TRUNC(t.plannedstartdate) <= TO_DATE('{1}','yyyy-mm-dd')
";
        JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
        sql = string.Format(sql, selectdate[0].ToString(), selectdate[1].ToString());
        //手动维护取消转工过滤 20250515
        sql+=@"AND NOt exists(SELECT 1 FROM ContainerStepNoShip@insitedb74 css
            LEFT JOIN container@insitedb74 cc ON cc.containerid=css.containerid
            LEFT JOIN workflowstep@insitedb74 ws ON ws.workflowstepid=css.workflowstepid
            WHERE cc.containername=t.containername AND ws.workflowstepname=t.workflowstepname AND css.noship=1)";
        //承制分厂
        if (jObject["tofactoryname"] != null && jObject["tofactoryname"].ToString() != "")
        {
            sql += string.Format(" AND NVL(f2.factoryname,t.tofactoryname) = '{0}'", jObject["tofactoryname"].ToString());
        }

        //主制分厂
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND NVL(f.factoryname,t.factoryname) = '{0}'", jObject["factoryname"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND t.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND t.containername = '{0}'", jObject["containername"].ToString());
        }

        sql += @"
UNION ALL 
SELECT vd.description factoryname,NULL fromworkcentername,rbs.materialnum productname,rbs.materialname productdescription,cci.containername,
       rbs.specname workflowstepname,e.fullname receiver,rbs.receivedate recivedate,
       CASE WHEN rbo.completedate IS NOT NULL THEN '已完成'
            WHEN rbs.receivedate IS NOT NULL THEN '已接收'
            ELSE '待接收' 
       END status,rbs.createtime stepplancreatedate,NULL senddate,NULL stepplanshipdate,NULL jjplaner,'已排产' planstatus,
       NULL rbplaner,rbs.planstartdate plannedstartdate,NULL needcompletedate,NULL estimatedcompletedate,rbo.completedate,
       NULL shipindate,NULL denydate,NULL denyer,NULL denyreason,
       CASE WHEN rbo.completedate IS NOT NULL THEN TRUNC(rbo.completedate) - TRUNC(rbs.planenddate)
            ELSE TRUNC(SYSDATE) - TRUNC(rbs.planenddate)
       END delay,
       CASE WHEN rbs.receivedate IS NOT NULL THEN TRUNC(rbs.receivedate) - TRUNC(rbs.planstartdate)
            ELSE TRUNC(SYSDATE) - TRUNC(rbs.planstartdate)
       END senddelaydays,
       NULL presend,rbs.planenddate plannedfinishdate,NULL notes
FROM rbordershipmanageinfo@insitedb74 rbs
LEFT JOIN vendor@insitedb74 vd ON vd.vendorname = rbs.suppliernum
LEFT JOIN containercurrentinfo@insitedb74 cci ON rbs.containerid = cci.containerid
LEFT JOIN rbordercontainerreceive@insitedb74 rbo ON rbo.containerid = rbs.containerid
LEFT JOIN employee@insitedb74 e ON e.employeeid = rbo.receiveemployeeid
LEFT JOIN factory@insitedb74 f ON f.factoryid = cci.originalfactoryid
WHERE TRUNC(rbs.planstartdate) >= TO_DATE('{0}','yyyy-mm-dd') AND TRUNC(rbs.planstartdate) <= TO_DATE('{1}','yyyy-mm-dd')
";
        //承制分厂
        if (jObject["tofactoryname"] != null && jObject["tofactoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["tofactoryname"].ToString());
        }

        //主制分厂
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND vd.description = '{0}'", jObject["factoryname"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND rbs.materialnum = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        sql = string.Format(sql, selectdate[0].ToString(), selectdate[1].ToString());
        return sql;
    }

    //统计承制分厂(热表厂)数据
    string getRbSqlString(JObject jObject, bool isSendOutNoDelayFilter = true)
    {
        string sql = @"
SELECT NVL(f.factoryname,t.factoryname) fromfactory,NVL(f2.factoryname,t.tofactoryname) tofactory,t.toworkcentername,t.productname,t.productdescription,
       t.containername,t.workflowstepname,
       t.stepplancreatedate,
       t.plannedstartdate,t.plannedfinishdate,t.senddate,t.recivedate toacceptdate,t.receiver,t.completedate,t.shipindate recivedate,
       CASE WHEN t.recivedate IS NOT NULL THEN TRUNC(t.recivedate) - TRUNC(t.plannedstartdate)
            ELSE TRUNC(SYSDATE) - TRUNC(t.plannedstartdate)
       END senddelaydays,
       CASE WHEN t.plannedfinishdate IS NOT NULL 
            THEN CASE WHEN t.completedate IS NOT NULL THEN TRUNC(t.completedate) - TRUNC(t.plannedfinishdate)
                  ELSE TRUNC(SYSDATE) - TRUNC(t.plannedfinishdate)
                 END 
            ELSE 0
       END finishdelaydays,nt.notes
FROM r_shipboard_jijia t
LEFT JOIN mes_factory_mapping@insitedb74 f ON f.mes_factoryname = t.factoryname
LEFT JOIN mes_factory_mapping@insitedb74 f2 ON f2.mes_factoryname = t.tofactoryname
LEFT JOIN containerchecksuspending@insitedb74 nt ON nt.containername = t.containername AND nt.workflowstepname = t.workflowstepname AND nt.isuse = 0
WHERE (CASE WHEN t.recivedate IS NOT NULL THEN TRUNC(t.recivedate) - TRUNC(t.plannedstartdate)
            ELSE TRUNC(SYSDATE) - TRUNC(t.plannedstartdate)
       END = 0
       OR
       CASE WHEN t.recivedate IS NOT NULL THEN TRUNC(t.recivedate) - TRUNC(t.plannedstartdate)
            ELSE TRUNC(SYSDATE) - TRUNC(t.plannedstartdate)
       END = -1) ";
        //承制分厂
        if (jObject["tofactoryname"] != null && jObject["tofactoryname"].ToString() != "")
        {
            sql += string.Format(" AND NVL(f2.factoryname,t.tofactoryname) = '{0}'", jObject["tofactoryname"].ToString());
        }

        //主制分厂
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND NVL(f.factoryname,t.factoryname) = '{0}'", jObject["factoryname"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND t.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND t.containername = '{0}'", jObject["containername"].ToString());
        }

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
            sql += string.Format(" AND TRUNC(t.plannedfinishdate) >= TO_DATE('{0}','yyyy-mm-dd') AND TRUNC(t.plannedfinishdate) <= TO_DATE('{1}','yyyy-mm-dd')", selectdate[0].ToString(), selectdate[1].ToString());
        }

        sql += @"
UNION ALL
SELECT vd.description fromfactory,'热表厂' tofactory,NULL toworkcentername,rbs.materialnum productname,rbs.materialname productdescription,
        cci.containername,rbs.specname workflowstepname,
        NULL stepplancreatedate,
        rbs.planstartdate plannedstartdate,
        rbs.planenddate plannedfinishdate,NULL senddate,rbo.receivedate toacceptdate,e.fullname receiver,rbo.completedate completedate,NULL recivedate,
        CASE WHEN rbo.receivedate IS NOT NULL THEN TRUNC(rbo.receivedate) - TRUNC(rbs.planstartdate)
            ELSE TRUNC(SYSDATE) - TRUNC(rbs.planstartdate)
        END senddelaydays,
        CASE WHEN rbo.completedate IS NOT NULL THEN TRUNC(rbo.completedate) - TRUNC(rbs.planenddate)
            ELSE TRUNC(SYSDATE) - TRUNC(rbs.planenddate)
        END finishdelaydays,
        NULL notes
FROM rbordershipmanageinfo@insitedb74 rbs
LEFT JOIN vendor@insitedb74 vd ON vd.vendorname = rbs.suppliernum
LEFT JOIN containercurrentinfo@insitedb74 cci ON rbs.containerid = cci.containerid
LEFT JOIN rbordercontainerreceive@insitedb74 rbo ON rbo.containerid = rbs.containerid
LEFT JOIN employee@insitedb74 e ON e.employeeid = rbo.receiveemployeeid 
LEFT JOIN factory@insitedb74 f ON f.factoryid = cci.originalfactoryid
WHERE 1=1 ";
        //承制分厂
        if (jObject["tofactoryname"] != null && jObject["tofactoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["tofactoryname"].ToString());
        }

        //主制分厂
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND vd.description = '{0}'", jObject["factoryname"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
            sql += string.Format(" AND TRUNC(rbs.planenddate) >= TO_DATE('{0}','yyyy-mm-dd') AND TRUNC(rbs.planenddate) <= TO_DATE('{1}','yyyy-mm-dd')",
                selectdate[0].ToString(), selectdate[1].ToString());
        }
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        //查询 计划发出数、准时发出数、准时到达率
        string sql = @"
SELECT  t.factoryname,
        COUNT(t.rn) valid,  --计划发出项（有效排产数）
        SUM(t.intimeQty) intime,    --准时发出数（准时到达项)
        ROUND(SUM(t.intimeQty)/COUNT(t.rn),4) * 100 intimeRate  --准时到达率
FROM (
    SELECT NVL(f.factoryname,t.factoryname) factoryname,t.recivedate,
           CASE WHEN t.recivedate IS NOT NULL THEN TRUNC(t.recivedate) - TRUNC(t.plannedstartdate)
            ELSE TRUNC(SYSDATE) - TRUNC(t.plannedstartdate)
           END senddelaydays,
           CASE WHEN t.recivedate IS NOT NULL AND (
             CASE WHEN t.recivedate IS NOT NULL THEN TRUNC(t.recivedate) - TRUNC(t.plannedstartdate)
               ELSE TRUNC(SYSDATE) - TRUNC(t.plannedstartdate)
             END = 0
             OR
             CASE WHEN t.recivedate IS NOT NULL THEN TRUNC(t.recivedate) - TRUNC(t.plannedstartdate)
               ELSE TRUNC(SYSDATE) - TRUNC(t.plannedstartdate)
             END = -1
           ) THEN 1 ELSE 0 END intimeQty,
           rownum rn
    FROM r_shipboard_jijia t
    LEFT JOIN mes_factory_mapping@insitedb74 f ON f.mes_factoryname = t.factoryname
    LEFT JOIN mes_factory_mapping@insitedb74 f2 ON f2.mes_factoryname = t.tofactoryname
    WHERE TRUNC(t.plannedstartdate) >= TO_DATE('{0}','yyyy-mm-dd') AND TRUNC(t.plannedstartdate) <= TO_DATE('{1}','yyyy-mm-dd')";
        JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
        sql = string.Format(sql, selectdate[0].ToString(), selectdate[1].ToString());
        //手动维护取消转工过滤 20250515
        sql+=@"AND NOt exists(SELECT 1 FROM ContainerStepNoShip@insitedb74 css
            LEFT JOIN container@insitedb74 cc ON cc.containerid=css.containerid
            LEFT JOIN workflowstep@insitedb74 ws ON ws.workflowstepid=css.workflowstepid
            WHERE cc.containername=t.containername AND ws.workflowstepname=t.workflowstepname AND css.noship=1)";
            
        //承制分厂
        if (jObject["tofactoryname"] != null && jObject["tofactoryname"].ToString() != "")
        {
            sql += string.Format(" AND NVL(f2.factoryname,t.tofactoryname) = '{0}'", jObject["tofactoryname"].ToString());
        }

        //主制分厂
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND NVL(f.factoryname,t.factoryname) = '{0}'", jObject["factoryname"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND t.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND t.containername = '{0}'", jObject["containername"].ToString());
        }

        sql += @"
) t
GROUP BY t.factoryname";

        sql = string.Format(sql, getJiJiaSql(jObject));
        DataTable dtResult = dal.GetDataTable(sql, "MESDB17");

        dtResult.Columns.Add("specCount");//计划完成数
        dtResult.Columns.Add("intimeFinishCount");//准时完成数
        dtResult.Columns.Add("intimeFinishRate");//准时完成率


        //主制分厂（扫码）数据
        string fromFactoryVendorSql = @"
SELECT  t.factoryname,
        COUNT(t.rn) valid,  --计划发出项（有效排产数）
        SUM(t.intimeQty) intime,    --准时发出数（准时到达项)
        ROUND(SUM(t.intimeQty)/COUNT(t.rn),4) * 100 intimeRate  --准时到达率
FROM (
     SELECT vd.description factoryname,
           rbo.receivedate,
           CASE WHEN rbo.receivedate IS NOT NULL THEN TRUNC(rbo.receivedate) - TRUNC(rbs.planstartdate)
                ELSE TRUNC(SYSDATE) - TRUNC(rbs.planstartdate)
           END senddelaydays,
           CASE WHEN rbo.receivedate IS NOT NULL AND (
             CASE WHEN rbo.receivedate IS NOT NULL THEN TRUNC(rbo.receivedate) - TRUNC(rbs.planstartdate)
               ELSE TRUNC(SYSDATE) - TRUNC(rbs.planstartdate)
             END = 0
             OR
             CASE WHEN rbo.receivedate IS NOT NULL THEN TRUNC(rbo.receivedate) - TRUNC(rbs.planstartdate)
               ELSE TRUNC(SYSDATE) - TRUNC(rbs.planstartdate)
             END = -1
           ) THEN 1 ELSE 0 END intimeQty,rownum rn
    FROM rbordershipmanageinfo rbs
    LEFT JOIN vendor vd ON vd.vendorname = rbs.suppliernum
    LEFT JOIN containercurrentinfo cci ON rbs.containerid = cci.containerid
    LEFT JOIN rbordercontainerreceive rbo ON rbo.containerid = rbs.containerid
    LEFT JOIN employee e ON e.employeeid = rbo.receiveemployeeid
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    WHERE TRUNC(rbs.planstartdate) >= TO_DATE('{0}','yyyy-mm-dd') AND TRUNC(rbs.planstartdate) <= TO_DATE('{1}','yyyy-mm-dd')";

        fromFactoryVendorSql = string.Format(fromFactoryVendorSql, selectdate[0].ToString(), selectdate[1].ToString());

        //承制分厂
        if (jObject["tofactoryname"] != null && jObject["tofactoryname"].ToString() != "")
        {
            fromFactoryVendorSql += string.Format(" AND f.factoryname = '{0}'", jObject["tofactoryname"].ToString());
        }

        //主制分厂
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            fromFactoryVendorSql += string.Format(" AND vd.description = '{0}'", jObject["factoryname"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            fromFactoryVendorSql += string.Format(" AND rbs.materialnum = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            fromFactoryVendorSql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        fromFactoryVendorSql += @"
) t
GROUP BY t.factoryname
";
        //主制分厂（扫码）数据
        DataTable dtFromFactoryVendorData = dal.GetDataTable(fromFactoryVendorSql);
        dtResult.Merge(dtFromFactoryVendorData);

        //承制分厂数据
        string toFactorySql = @"
SELECT t.fromfactory factoryname,
       COUNT(1) specCount,
       SUM(CASE WHEN t.completedate IS NOT NULL AND t.finishdelaydays <= 0 THEN 1 ELSE 0 END) intimeFinishCount,
       ROUND(SUM(CASE WHEN t.completedate IS NOT NULL AND t.finishdelaydays <= 0 THEN 1 ELSE 0 END) / COUNT(1),4) * 100 intimeFinishRate
FROM (
    {0}
) t 
GROUP BY t.fromfactory";
        toFactorySql = string.Format(toFactorySql, getRbSqlString(jObject));
        DataTable dtShipPlan = dal.GetDataTable(toFactorySql, "MESDB17");
        
        foreach (DataRow dr in dtResult.Rows)
        {
            DataRow[] drs = dtShipPlan.Select(string.Format("factoryname='{0}'", dr["factoryname"].ToString()));
            if (drs.Length > 0)
            {
                dr["specCount"] = drs[0]["specCount"].ToString();
                dr["intimeFinishRate"] = drs[0]["intimeFinishRate"].ToString();
                dr["intimeFinishCount"] = drs[0]["intimeFinishCount"].ToString();
            }
            else
            {
                dr["specCount"] = 0;
                dr["intimeFinishRate"] = 0;
                dr["intimeFinishCount"] = 0;
            }
        }

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //机加分厂明细数据
    Result getJiJiaDetail(JObject jObject)
    {
        string sql = @" SELECT  t.* FROM ( {0} ) t";
        sql = string.Format(sql, getJiJiaSql(jObject));
        if (jObject["seriesName"] != null && jObject["seriesName"].ToString() == "准时发出数")
        {
            sql += @" WHERE t.recivedate IS NOT NULL AND (
             CASE WHEN t.recivedate IS NOT NULL THEN TRUNC(t.recivedate) - TRUNC(t.plannedstartdate)
               ELSE TRUNC(SYSDATE) - TRUNC(t.plannedstartdate)
             END = 0
             OR
             CASE WHEN t.recivedate IS NOT NULL THEN TRUNC(t.recivedate) - TRUNC(t.plannedstartdate)
               ELSE TRUNC(SYSDATE) - TRUNC(t.plannedstartdate)
             END = -1)";
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"], "MESDB17");
        DataTable dt = dto.DBTable;

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //热表厂明细数据
    Result getRbDetail(JObject jObject)
    {
        string sql = @"SELECT  t.* FROM ( {0} ) t";
        sql = string.Format(sql, getRbSqlString(jObject));

        if (jObject["seriesName"] != null && jObject["seriesName"].ToString() == "准时完成数")
        {
            sql += " WHERE t.finishdelaydays <= 0 ";
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"], "MESDB17");
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出机加分厂明细数据
    void exportJijiaDetail(JObject jObject)
    {
        string sql = @" SELECT  t.* FROM ( {0} ) t";
        sql = string.Format(sql, getJiJiaSql(jObject));
        if (jObject["seriesName"] != null && jObject["seriesName"].ToString() == "准时发出数")
        {
            sql += @" WHERE t.recivedate IS NOT NULL AND (
             CASE WHEN t.recivedate IS NOT NULL THEN TRUNC(t.recivedate) - TRUNC(t.plannedstartdate)
               ELSE TRUNC(SYSDATE) - TRUNC(t.plannedstartdate)
             END = 0
             OR
             CASE WHEN t.recivedate IS NOT NULL THEN TRUNC(t.recivedate) - TRUNC(t.plannedstartdate)
               ELSE TRUNC(SYSDATE) - TRUNC(t.plannedstartdate)
             END = -1)";
        }

        DataTable dtResult = dal.GetDataTable(sql, "MESDB17");
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        dtResult.Columns["factoryname"].Caption = "主制分厂";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["workflowstepname"].Caption = "转工工序";
        dtResult.Columns["receiver"].Caption = "接收人";
        dtResult.Columns["recivedate"].Caption = "接收时间";
        dtResult.Columns["status"].Caption = "转工状态";
        dtResult.Columns["stepplancreatedate"].Caption = "机加排产时间";
        dtResult.Columns["senddate"].Caption = "实际发出时间";
        dtResult.Columns["stepplanshipdate"].Caption = "承制分厂排产时间";
        dtResult.Columns["jjplaner"].Caption = "机加排产人";
        dtResult.Columns["planstatus"].Caption = "排产状态";
        dtResult.Columns["rbplaner"].Caption = "承制分厂排产人";
        dtResult.Columns["plannedstartdate"].Caption = "计划发出时间";
        dtResult.Columns["needcompletedate"].Caption = "要求完成时间";
        dtResult.Columns["estimatedcompletedate"].Caption = "预计完成时间";
        dtResult.Columns["completedate"].Caption = "完成时间";
        dtResult.Columns["shipindate"].Caption = "转入时间";
        dtResult.Columns["denydate"].Caption = "拒收时间";
        dtResult.Columns["denyer"].Caption = "拒收人";
        dtResult.Columns["denyreason"].Caption = "拒收原因";
        dtResult.Columns["delay"].Caption = "完成超期时间";
        dtResult.Columns["senddelaydays"].Caption = "发出超期时间";
        dtResult.Columns["presend"].Caption = "发出提前时间";
        dtResult.Columns["plannedfinishdate"].Caption = "计划完成时间";
        dtResult.Columns["fromworkcentername"].Caption = "责任工区";
        dtResult.Columns["notes"].Caption = "备注";
        ExportHelper.ExportExcel(dtResult, jObject["factoryname"].ToString() + "【" + jObject["seriesName"].ToString() + "】明细");
    }

    //导出热表分厂明细数据
    void exportRbDetail(JObject jObject)
    {
        string sql = @" SELECT  t.* FROM ( {0} ) t";
        sql = string.Format(sql, getRbSqlString(jObject));

        if (jObject["seriesName"] != null && jObject["seriesName"].ToString() == "准时完成数")
        {
            sql += " WHERE t.finishdelaydays <= 0 ";
        }

        DataTable dtResult = dal.GetDataTable(sql, "MESDB17");
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["fromfactory"].Caption = "主制分厂";
        dtResult.Columns["tofactory"].Caption = "承制分厂";
        dtResult.Columns["toworkcentername"].Caption = "承制工区";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["workflowstepname"].Caption = "排产工序";
        dtResult.Columns["plannedstartdate"].Caption = "plannedstartdate";
        dtResult.Columns["senddate"].Caption = "实际发出时间";
        dtResult.Columns["toacceptdate"].Caption = "接收时间";
        dtResult.Columns["receiver"].Caption = "接收人";
        dtResult.Columns["completedate"].Caption = "实际完成时间";
        dtResult.Columns["recivedate"].Caption = "转入时间";
        dtResult.Columns["finishdelaydays"].Caption = "完成超期时间";
        dtResult.Columns["plannedstartdate"].Caption = "计划发出时间";
        dtResult.Columns["plannedfinishdate"].Caption = "计划完成时间";
        dtResult.Columns["stepplancreatedate"].Caption = "排产日期";
        dtResult.Columns["senddelaydays"].Caption = "发出超期时间";
        dtResult.Columns["notes"].Caption = "备注";
        ExportHelper.ExportExcel(dtResult, jObject["factoryname"].ToString() + "【" + jObject["seriesName"].ToString() + "】明细");
    }
}